package com.ai.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.ai.dbutil.DBconn;
import com.ai.entity.Member;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class MemberDao {
	/*
	 * if success returns affected rows in database which is positive,
	 * else returns 0.
	 */
	public int addMember(Member mem) {
		Connection conn = DBconn.getInstance();
		int i = 0;
		String sql = "insert into member (NAME, ADDRESS, JOB) values (?,?,?)";
		PreparedStatement pstmt;
		try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, mem.getName());
	        pstmt.setString(2, mem.getAddress());
	        pstmt.setString(3, mem.getJob());
	        i = pstmt.executeUpdate();
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
		
	    return i;
	}
	
	public int getId(Member mem) {
		Connection conn = DBconn.getInstance();
		int i = 0;
		PreparedStatement pstmt;
		String sql = "select MEMBER_ID from member where NAME=? and ADDRESS=? and job=?";
		try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, mem.getName());
	        pstmt.setString(2, mem.getAddress());
	        pstmt.setString(3, mem.getJob());
	        ResultSet rs = pstmt.executeQuery();
	        if(rs.next()) {
	        	i = rs.getInt("MEMBER_ID");
	        }
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
		return i;
	}
	
	public int getIdByName(String name) {
		Connection conn = DBconn.getInstance();
		int i = 0;
		String sql = "select MEMBER_ID from member where NAME=?";
		PreparedStatement pstmt;
		try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, name);
	        ResultSet rs = pstmt.executeQuery();
	        if(rs.next()) {
	        	i = rs.getInt("MEMBER_ID");
	        }
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
		return i;
	}
	
	
	

	public String getNameById(int id) {
		String name = null;
		Connection conn = DBconn.getInstance();
	    String sql = "select NAME from MEMBER where MEMBER_ID=" + id;
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        if(rs.next()) {
	        	name = rs.getString("NAME");
	        }
	            
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
		return name;
	}

}
